const express = require('express');
const router = express.Router();
const { PrismaClient } = require('@prisma/client');
const { Prisma } = require('@prisma/client');

const prisma = new PrismaClient();

// 从数据库获取表结构
async function getDatabaseTableMetadata() {
  const tableName = 'people';
  
  // 使用原始SQL查询获取表结构信息
  const tableInfo = await prisma.$queryRaw`
    SELECT 
      column_name, 
      data_type, 
      is_nullable, 
      column_default
    FROM 
      information_schema.columns
    WHERE 
      table_name = ${tableName}
    ORDER BY 
      ordinal_position;
  `;
  
  // 字段名称到显示名称的映射
  const displayNameMap = {
    id: 'ID',
    name: '姓名',
    gender: '性别',
    birthDate: '出生日期',
    hometown: '籍贯',
    rank: '职位',
    unit: '部门',
    employmentDate: '入职日期',
    contact: '联系方式',
    score: '成绩',
    status: '状态',
    created_at: '创建时间',
    updated_at: '更新时间'
  };
  
  // 字段宽度配置
  const widthMap = {
    id: 80,
    name: 120,
    gender: 80,
    birthDate: 120,
    hometown: 150,
    rank: 150,
    unit: 180,
    employmentDate: 120,
    contact: 150,
    score: 120,
    status: 100,
    created_at: 150,
    updated_at: 150
  };
  
  // 创建字段元数据
  const metadata = tableInfo
    .filter(column => !['created_at', 'updated_at'].includes(column.column_name))
    .map(column => {
      // 确定字段类型
      let type = 'text';
      if (['int', 'integer', 'numeric', 'decimal'].includes(column.data_type)) type = 'number';
      if (column.column_name.includes('date') || ['date', 'timestamp'].includes(column.data_type)) type = 'date';
      
      return {
        name: column.column_name,
        displayName: displayNameMap[column.column_name] || column.column_name,
        width: widthMap[column.column_name] || 120,
        sortable: true,
        required: column.is_nullable === 'NO',
        type: type
      };
    });
  
  return metadata;
}

// 获取表格元数据
router.get('/metadata', async (req, res) => {
  try {
    const metadata = await getDatabaseTableMetadata();
    res.json(metadata);
    // console.log(metadata);
  } catch (error) {
    console.error('获取表格元数据失败:', error);
    res.status(500).json({ message: '获取表格元数据失败' });
  }
});

// 获取所有人员数据
router.get('/', async (req, res) => {
  try {
    const people = await prisma.people.findMany({
      orderBy: { id: 'asc' }
    });
    res.json(people);
  } catch (error) {
    console.error('获取人员列表失败:', error);
    res.status(500).json({ message: '获取人员列表失败' });
  }
});

// 添加新人员
router.post('/', async (req, res) => {
  try {
    const { name, gender, birthDate, hometown, rank, unit, employmentDate, contact, score, status } = req.body;
    
    // 验证必填字段
    if (!name || !gender || !birthDate || !hometown || !rank || !unit || !employmentDate) {
      return res.status(400).json({ message: '必填字段不能为空' });
    }
    
    const newPerson = await prisma.people.create({
      data: {
        name,
        gender,
        birthDate,
        hometown,
        rank,
        unit,
        employmentDate,
        contact,
        score: score ? parseInt(score) : null,
        status: status || '在职'
      }
    });
    
    res.status(201).json(newPerson);
  } catch (error) {
    console.error('创建人员失败:', error);
    res.status(500).json({ message: '创建人员失败' });
  }
});

// 更新人员信息
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, gender, birthDate, hometown, rank, unit, employmentDate, contact, score, status } = req.body;
    
    // 验证必填字段
    if (!name || !gender || !birthDate || !hometown || !rank || !unit || !employmentDate) {
      return res.status(400).json({ message: '必填字段不能为空' });
    }
    
    const updatedPerson = await prisma.people.update({
      where: { id: parseInt(id) },
      data: {
        name,
        gender,
        birthDate,
        hometown,
        rank,
        unit,
        employmentDate,
        contact,
        score: score ? parseInt(score) : null,
        status: status || '在职'
      }
    });
    
    res.json(updatedPerson);
  } catch (error) {
    console.error('更新人员失败:', error);
    if (error.code === 'P2025') {
      return res.status(404).json({ message: '人员不存在' });
    }
    res.status(500).json({ message: '更新人员失败' });
  }
});

// 删除人员
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    
    await prisma.people.delete({
      where: { id: parseInt(id) }
    });
    
    res.json({ message: '删除成功', id: parseInt(id) });
  } catch (error) {
    console.error('删除人员失败:', error);
    if (error.code === 'P2025') {
      return res.status(404).json({ message: '人员不存在' });
    }
    res.status(500).json({ message: '删除人员失败' });
  }
});

// 搜索人员（支持分页、排序和筛选）
router.post('/search', async (req, res) => {
  try {
    const { page = 1, pageSize = 10, searchValue = '', sortField, sortOrder } = req.body;
    
    // 构建查询条件
    const where = searchValue 
      ? {
          OR: [
            { name: { contains: searchValue, mode: 'insensitive' } },
            { unit: { contains: searchValue, mode: 'insensitive' } }
          ]
        }
      : {};
    
    // 构建排序条件
    const orderBy = sortField 
      ? { [sortField]: sortOrder === 'descend' ? 'desc' : 'asc' }
      : { id: 'asc' };
    
    // 执行查询
    const [total, data] = await Promise.all([
      prisma.people.count({ where }),
      prisma.people.findMany({
        where,
        orderBy,
        skip: (page - 1) * pageSize,
        take: pageSize
      })
    ]);
    
    res.json({
      total,
      data,
      page,
      pageSize
    });
  } catch (error) {
    console.error('搜索人员失败:', error);
    res.status(500).json({ message: '搜索人员失败' });
  }
});

// 获取字段可选值（如部门、职位等）
router.get('/field-options/:fieldName', async (req, res) => {
  try {
    const { fieldName } = req.params;
    
    // 验证字段名是否有效
    const validFields = ['unit', 'rank', 'gender', 'status', 'hometown'];
    if (!validFields.includes(fieldName)) {
      return res.status(400).json({ message: '无效的字段名' });
    }
    
    // 查询该字段的所有不同值
    const distinctValues = await prisma.people.findMany({
      select: {
        [fieldName]: true
      },
      distinct: [fieldName]
    });
    
    // 提取不同值并排序
    const options = distinctValues
      .map(item => item[fieldName])
      .filter(value => value) // 过滤掉可能的null值
      .sort();
    
    res.json({ field: fieldName, options });
  } catch (error) {
    console.error(`获取 ${req.params.fieldName} 字段选项失败:`, error);
    res.status(500).json({ message: `获取字段选项失败` });
  }
});

module.exports = router;
